﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MINERP.COM;

namespace MINERP.DAL.GoodsDelivery
{
    public class GoodsReturnDAL
    {
        string sqlCon = ConfigurationManager.ConnectionStrings["sqlCon"].ToString();

        #region 地区列表
        /// <summary>
        /// 地区列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetAreas()
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select distinct Area from shop";
                SqlCommand cmd = new SqlCommand(sql, con);
                List<string> areas = new List<string>();
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    areas.Add(reader[0].ToString());
                }
                return areas;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 新增退件包记录
        /// <summary>
        /// 新增退件包记录
        /// </summary>
        public void InsertReturnPackage(string area, int stationId, int stationGoodsId, int userId, int status)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "insert into ReturnPackage(area,stationId,stationGoodsId,insertTime,userId,status)";
                sql += " values('" + area + "'," + stationId + "," + stationGoodsId + ",getdate()," + userId + "," + status + ")";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改退件包
        /// <summary>
        /// 修改退件包
        /// </summary>
        public void UpdateReturnPackage(string area, int stationId, int stationGoodsId, int userId, int returnId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "update returnpackage set area='" + area + "',stationid=" + stationId + ",stationgoodsid=" + stationGoodsId + ",userid=" + userId + " where returnid=" + returnId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 退件包(按状态)
        /// <summary>
        /// 退件包(按状态)
        /// </summary>
        /// <returns></returns>
        public DataTable GetReturnPackage(int status)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "SELECT ReturnPackage.ReturnId, ReturnPackage.Area, DeliverStation.StationName, DeliverStationGoods.StationGoodsNamePhone, ReturnPackage.InsertTime,users.UserName";
                sql += " FROM ReturnPackage INNER JOIN";
                sql += " DeliverStation ON ReturnPackage.stationId = DeliverStation.stationId INNER JOIN";
                sql += " DeliverStationGoods ON ReturnPackage.stationGoodsId = DeliverStationGoods.stationGoodsId INNER JOIN";
                sql += " users ON ReturnPackage.userId = users.userID";
                sql += " WHERE ReturnPackage.status = " + status;
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取已取件的退件包
        /// <summary>
        /// 获取已取件的退件包
        /// </summary>
        /// <param name="status"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable GetReturnPackageFetched(int status, DateTime start, DateTime end)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "SELECT ReturnPackage.ReturnId, ReturnPackage.Area, DeliverStation.StationName, DeliverStationGoods.StationGoodsNamePhone, ReturnPackage.InsertTime,users.UserName";
                sql += " FROM ReturnPackage INNER JOIN";
                sql += " DeliverStation ON ReturnPackage.stationId = DeliverStation.stationId INNER JOIN";
                sql += " DeliverStationGoods ON ReturnPackage.stationGoodsId = DeliverStationGoods.stationGoodsId INNER JOIN";
                sql += " users ON ReturnPackage.userId = users.userID";
                sql += " WHERE insertTime>='" + start + "' and insertTime<='" + end + "' and ReturnPackage.status = " + status;
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 删除退件包
        /// <summary>
        /// 删除退件包
        /// </summary>
        /// <param name="returnId"></param>
        public void DelReturnPackage(int returnId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql1 = "delete from returnpackage where returnid=" + returnId;
                string sql2 = "delete from returnorderinfo where returnid=" + returnId ;
                SqlCommand cmd = new SqlCommand();
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                try
                {
                    cmd.Connection = con;
                    cmd.Transaction = tran;
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = sql1;
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception exx)
                {
                    tran.Rollback();
                    throw exx;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 新增退件产品记录
        /// <summary>
        /// 新增退件产品记录
        /// </summary>
        /// <param name="returnId"></param>
        /// <param name="orderProductId"></param>
        /// <param name="fetchUserId"></param>
        /// <param name="returnReason"></param>
        public void InsertReturnOrderInfo(int returnId, int orderProductId, int fetchUserId, string returnReason)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                int notFetch = (int)ReturnOrderInfoStatus.NotFetch;
                string sql = "insert into ReturnOrderInfo(returnId,orderProductId,fetchUserId,disposeUserId,returnReason,fetchTime,status)";
                sql += " values(" + returnId + "," + orderProductId + "," + fetchUserId + "," + fetchUserId + ",'" + returnReason + "',getdate()," + notFetch + ")";
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 验证产品
        /// <summary>
        /// 验证产品
        /// </summary>
        /// <param name="orderProductId"></param>
        /// <returns></returns>
        public DataTable ConfirmOrderInfo(int orderProductId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select OrderProductId,ShopName,CtmName,OrderProductName from orderInfo_bk,tb_orderbag_bk,shop where orderinfo_bk.orderproductid=" + orderProductId;
                sql += " and orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid and tb_orderbag_bk.shopid=shop.shopid";
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取退件包内的产品集合
        /// <summary>
        /// 获取退件包内的产品集合
        /// </summary>
        /// <param name="returnId"></param>
        /// <returns></returns>
        public DataTable GetOrderProductInReturnPackage(int returnId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select * from ReturnOrderInfo where returnid=" + returnId;        //这里需要修改
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取退件产品(退件包号，产品号)
        /// <summary>
        /// 获取退件产品(退件包号，产品号)
        /// </summary>
        public DataTable GetReturnOrderInfo(int returnId, int orderProductId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select shop.ShopName,orderInfo_bk.CtmName,ReturnInfoId,orderinfo_bk.OrderProductId,OrderProductName,FetchUserId,ReturnReason from ReturnOrderInfo,orderinfo_bk,tb_orderbag_bk,shop where returnid=" + returnId + " and returnorderinfo.orderproductid=" + orderProductId+" and returnorderinfo.orderproductid=orderinfo_bk.orderproductid and orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid and tb_orderbag_bk.shopid=shop.shopid";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取退件包内的所有退件产品
        /// <summary>
        /// 获取退件包内的所有退件产品
        /// </summary>
        /// <param name="returnId"></param>
        /// <returns></returns>
        public DataTable GetReturnOrderInfos(int returnId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select ShopName,CtmName,orderInfo_bk.OrderProductId,OrderProductName,userName as FetchUserName,returnOrderInfo.FetchTime,ReturnReason,returnOrderInfo.Status";
                sql += " from returnOrderInfo,orderinfo_bk,tb_orderbag_bk,shop,users where returnorderinfo.orderproductid=orderinfo_bk.orderproductid";
                sql += " and orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid and tb_orderbag_bk.shopid=shop.shopid and returnorderinfo.fetchuserid=users.userid and returnid=" + returnId;
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改退件产品
        /// <summary>
        /// 修改退件产品
        /// </summary>
        public void ModifyReturnOrderInfo(int returnOrderInfoId, int fetchUserId, string returnReason)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "update ReturnOrderInfo set fetchUserId=" + fetchUserId + ",returnReason='" + returnReason + "' where returnInfoId=" + returnOrderInfoId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 删除退件产品
        /// <summary>
        /// 删除退件产品
        /// </summary>
        /// <param name="returnId"></param>
        /// <param name="orderProductId"></param>
        public void DelReturnOrderInfo(int returnId, int orderProductId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "delete from ReturnOrderInfo where returnId=" + returnId + " and orderproductid=" + orderProductId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改退件包状态未已取件
        /// <summary>
        /// 修改退件包状态未已取件
        /// </summary>
        public void ModifyReturnPackageStatusToFetch(int returnPackageStatus, int returnOrderInfoStatus, int returnId)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            string sql1 = "update ReturnOrderInfo set status=" + returnOrderInfoStatus + " where returnId=" + returnId;
            string sql2 = "update ReturnPackage set status=" + returnPackageStatus + " where returnid=" + returnId;
            SqlCommand cmd = con.CreateCommand();
            cmd.Connection = con;
            try
            {
                con.Open();
                SqlTransaction tran = con.BeginTransaction();
                try
                {
                    cmd.Transaction = tran;
                    cmd.CommandText = sql1;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = sql2;
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改退件包状态
        /// <summary>
        /// 修改退件包状态
        /// </summary>
        public void UpdateReturnPackageStatus(int returnId, int status)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "update ReturnPackage set status="+status+" where returnid="+returnId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 获取退件产品(按状态)
        /// <summary>
        /// 获取退件产品(按状态)
        /// </summary>
        /// <param name="status"></param>
        /// <returns></returns>
        public DataTable GetReturnOrderInfosByStatus(int status,DateTime start,DateTime end)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select ReturnInfoId,shop.ShopName,orderInfo_bk.CtmName,orderInfo_bk.OrderProductName,orderInfo_bk.OrderProductId,users.userName as FetchUserName,ReturnReason,returnOrderInfo.FetchTime,users_1.username as DisposeUserName,DisposeRecord,DisposeTime";
                sql += " from returnorderinfo inner join orderinfo_bk on returnorderinfo.orderproductid=orderinfo_bk.orderproductid";
                sql += " inner join tb_orderbag_bk on orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid ";
                sql += " inner join shop on tb_orderbag_bk.shopid=shop.shopid";
                sql += " inner join users on returnorderinfo.fetchuserid=users.userid";
                sql += " inner join users as users_1 on returnorderinfo.disposeuserid=users_1.userid";
                sql += " where returnorderinfo.status="+status;
                int disposeStatus = (int)ReturnOrderInfoStatus.Disposed;
                if (status == disposeStatus)
                {
                    sql += " and disposeTime>='" + start + "'and disposetime<='" + end + "'";
                }
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adaper = new SqlDataAdapter(cmd);
                con.Open();
                adaper.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 处理退件产品
        /// <summary>
        /// 处理退件产品
        /// </summary>
        public void DisposeReturnOrderInfo(int returnInfoId, int disposeUserId, string disposeRecord,int status)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "update returnorderinfo set disposeuserid="+disposeUserId+",disposerecord='"+disposeRecord+"',disposetime=getdate(),status="+status+" where returninfoid="+returnInfoId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            { 
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 修改退件产品状态
        /// <summary>
        /// 修改退件产品状态
        /// </summary>
        public void ModifyReturnOrderInfoStatus(int returnInfoId, int status)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "update returnorderinfo set status="+status+" where returninfoid="+returnInfoId;
                SqlCommand cmd = new SqlCommand(sql, con);
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 查询
        /// <summary>
        /// 查询
        /// </summary>
        public DataTable FindReturnOrderInfo(int shopId, string ctmName, DateTime start, DateTime end)
        {
            SqlConnection con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select ReturnInfoId,shop.ShopName,orderInfo_bk.CtmName,orderInfo_bk.OrderProductName,orderInfo_bk.OrderProductId,users.userName as FetchUserName,ReturnReason,returnOrderInfo.FetchTime,users_1.username as DisposeUserName,DisposeRecord,DisposeTime,ReturnOrderInfo.Status";
                sql += " from returnorderinfo inner join orderinfo_bk on returnorderinfo.orderproductid=orderinfo_bk.orderproductid";
                sql += " inner join tb_orderbag_bk on orderinfo_bk.orderbagid=tb_orderbag_bk.orderbagid ";
                sql += " inner join shop on tb_orderbag_bk.shopid=shop.shopid";
                sql += " inner join users on returnorderinfo.fetchuserid=users.userid";
                sql += " inner join users as users_1 on returnorderinfo.disposeuserid=users_1.userid";
                sql += " where shop.ShopId=" + shopId + " and returnorderinfo.fetchTime>='" + start + "' and returnorderinfo.fetchtime<='" + end + "'";
                if (ctmName.Length > 0)
                {
                    sql += " and orderinfo_bk.ctmname like '%" + ctmName + "%'";
                }
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
    }
}
